package com.tonghe.dao.user;

import com.mysql.cj.PerConnectionLRUFactory;
import com.mysql.cj.util.StringUtils;
import com.tonghe.dao.BaseDao;
import com.tonghe.pojo.Role;
import com.tonghe.pojo.User;
import org.junit.Test;

import javax.swing.plaf.basic.BasicOptionPaneUI;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Date:2021/12/2- 12- 02- 20:39
 **/
public class UserDaoImpl implements UserDao{
    @Override
    public User getLoginUser(Connection connection, String userCode) throws SQLException{
        //
        PreparedStatement pstm = null;
        ResultSet rs = null;
        User user = null;
        //
        if(connection != null){
            String sql = "select * from smbms_user where userCode = ?";
            Object[] params = {userCode};
            rs = BaseDao.execute(connection,pstm,rs,sql,params );
                if(rs.next()){
                    user = new User();
                    user.setId(rs.getInt("id"));
                    user.setUserCode(rs.getString("userCode"));
                    user.setUserName(rs.getString("userName"));
                    user.setUserPassword(rs.getString("userPassword"));
                    user.setGender(rs.getInt("gender"));
                    user.setBirthday(rs.getDate("birthday"));
                    user.setPhone(rs.getString("phone"));
                    user.setAddress(rs.getString("address"));
                    user.setUserRole(rs.getInt("userRole"));
                    user.setCreatedBy(rs.getInt("createdBy"));
                    user.setCreationDate(rs.getTimestamp("creationDate"));
                    user.setModifyBy(rs.getInt("modifyBy"));
                    user.setModifyDate(rs.getTimestamp("modifyDate"));
                }
                BaseDao.closeResource(null,pstm,rs);
        }
        return  user;
    }

    @Override
    public int updatePwd( Connection connection, int id, String userPassword) throws SQLException {
        //System.out.println("dao"+userPassword);

        PreparedStatement pstm = null;
//        Connection connection = null;
        int ex = 0;
        if(connection != null){
            String sql = "update smbms_user set userPassword = ? where id = ?";
            Object params[] = {userPassword , id};
            ex = BaseDao.execute(connection,pstm,sql,params);
            BaseDao.closeResource(null,pstm,null);
        }
        return ex;
    }

    /*
    * 根据用户名或者角色查询用户总数【最难李姐的sql】
    * */
    @Override
    public int getUserCount(Connection connection, String username, int userRole) throws SQLException {

        int count = 0;
        PreparedStatement pstm = null;
        ResultSet rs = null;

        if(connection != null){

            StringBuffer sql = new StringBuffer();
            sql.append("select count(1) as count from smbms_user u,smbms_role r where u.userRole = r.id ");
            ArrayList<Object> list = new ArrayList<Object>();//存放参数

            if(!StringUtils.isNullOrEmpty(username)){
                sql.append(" and u.userName like ? ");
                list.add("%"+username+"%");  //index: 0
            }
            if(userRole > 0){
                sql.append(" and u.userRole = ? ");
                list.add(userRole);
            }
            //把list转为数组
            Object[] params = list.toArray();
            System.out.println("UserDaoImpl -> getUserCount : "+sql.toString());//完整的sql语句

            rs = BaseDao.execute(connection,pstm,rs,sql.toString(),params);
            if(rs.next()){
                count = rs.getInt("count");//从结果集中或许最终的数量count
            }
            BaseDao.closeResource(null,pstm,rs);
        }

        return count;
    }

//    @Override
//    public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws SQLException {
//
//        PreparedStatement pstm = null;
//        ResultSet rs = null;
//        List<User> userList = new ArrayList<User>();
//
//        if(connection != null){
//            StringBuffer sql = new StringBuffer();
//            sql.append("select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.userRole = r.id");
//            ArrayList<Object> list = new ArrayList<Object>();
//            if(!StringUtils.isNullOrEmpty(userName)){
//                sql.append(" and u.userName like ? ");
//                list.add("%"+userName+"%");
//            }
//            if(userRole > 0){
//                sql.append(" and u.userRole = ? ");
//                list.add(userRole);
//            }
//            sql.append(" order by creationDate DESC limit ?, ? ");
//            currentPageNo = (currentPageNo - 1)*pageSize;
//            list.add(currentPageNo);
//            list.add(pageSize);
//
//            Object[] params = list.toArray();
//            System.out.println("getUserList sql->"+sql.toString());
//            rs = BaseDao.execute(connection,pstm,rs,sql.toString(),params);
//            while(rs.next()){
//                User _user = new User();
//                _user.setId(rs.getInt("id"));
//                _user.setUserCode(rs.getString("userCode"));
//                _user.setUserName(rs.getString("userName"));
//                _user.setGender(rs.getInt("gender"));
//                _user.setBirthday(rs.getDate("birthday"));
//                _user.setPhone(rs.getString("phone"));
//                _user.setUserRole(rs.getInt("userRole"));
//                _user.setUserRoleName(rs.getString("userRoleName"));
//                userList.add(_user);
//            }
//            BaseDao.closeResource(null,pstm,rs);
//        }
//        return userList;
//    }
@Override
public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws SQLException {
    PreparedStatement pstm = null;
    ResultSet rs = null;
    List<User> userList = new ArrayList<User>();

    if(connection != null){
        StringBuffer sql = new StringBuffer();
        sql.append("select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.userRole = r.id");
        List<Object> list = new ArrayList<Object>();
        if(!StringUtils.isNullOrEmpty(userName)){
            sql.append(" and u.userName like ?");
            list.add("%"+userName+"%");
        }
        if(userRole > 0){
            sql.append(" and u.userRole = ?");
            list.add(userRole);
        }
        //数据库中 分页使用limit
        sql.append(" order by creationDate DESC limit ?,?");
        currentPageNo = (currentPageNo-1)*pageSize;
        list.add(currentPageNo);
        list.add(pageSize);

        Object[] params = list.toArray();

//        rs = BaseDao.execute(connection, pstm, sql.toString(), params, rs);
        rs = BaseDao.execute(connection,pstm,rs,sql.toString(),params);
        while(rs.next()){
            User _user = new User();
            _user.setId(rs.getInt("id"));
            _user.setUserCode(rs.getString("userCode"));
            _user.setUserName(rs.getString("userName"));
            _user.setGender(rs.getInt("gender"));
            _user.setBirthday(rs.getDate("birthday"));
            _user.setPhone(rs.getString("phone"));
            _user.setUserRole(rs.getInt("userRole"));
            _user.setUserRoleName(rs.getString("userRoleName"));
            userList.add(_user);
        }
        System.out.println("sql ----> " + sql.toString());
        BaseDao.closeResource(null,pstm,rs);
    }
    return userList;
}

    @Override
    public int add(Connection connection, User user) throws SQLException {
        PreparedStatement pstm = null;
        int updateRows = 0;
        if(connection != null){
            String sql = "insert into smbms_user (userCode,userName,userPassword,userRole," +
                    "gender,birthday,phone,address,creationDate,createdBy)" +
                    "values(?,?,?,?,?,?,?,?,?,?)";

            Object[] params = {user.getUserCode(),user.getUserName(),user.getUserPassword(),
                    user.getUserRole(),user.getGender(),user.getBirthday(),
                    user.getPhone(),user.getAddress(),user.getCreationDate(),user.getCreatedBy()};
            updateRows = BaseDao.execute(connection, pstm,sql,params);
            BaseDao.closeResource(null,pstm,null);
        }
        return updateRows;
    }


    @Test
    public void tese(){
        Connection con = BaseDao.getConnection();
        UserDaoImpl us = new UserDaoImpl();
        User user = null;
        try {
            user = us.getLoginUser(con,"admin");
            //System.out.println("get了");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(user.getAddress());
    }
    @Test
    public void test1(){
        Connection connection = BaseDao.getConnection();
         UserDaoImpl us = new UserDaoImpl();
         List<User> userList = null;
        try {
            userList = us.getUserList(connection,null,0,1,5);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        BaseDao.closeResource(connection,null,null);
        System.out.println(userList);
    }
}
